"""
SQLite EmailQA_SOP Repository Implementation
"""

import sqlite3
import os
from typing import List, Optional
from datetime import datetime

from ..domain.emailqa_sop import EmailQA_SOP
from ..domain.emailqa_sop_repository import EmailQA_SOPRepository

class SQLiteEmailQA_SOPRepository(EmailQA_SOPRepository):
    """SQLite implementation of EmailQA_SOP repository"""
    
    def __init__(self, db_path: str = "emailqa_sop.db"):
        self.db_path = db_path
        self._init_database()
    
    def _init_database(self):
        """Initialize database with schema"""
        if not os.path.exists(self.db_path):
            # Create the database file
            conn = sqlite3.connect(self.db_path)
            conn.close()
        
        # Execute schema
        script_dir = os.path.dirname(os.path.dirname(os.path.dirname(__file__)))
        sql_file = os.path.join(script_dir, 'sql', 'sqlite', '002_emailqa_sop.sql')
        
        with sqlite3.connect(self.db_path) as conn:
            with open(sql_file, 'r', encoding='utf-8') as f:
                conn.executescript(f.read())
            conn.commit()
    
    def _get_connection(self) -> sqlite3.Connection:
        """Get database connection"""
        conn = sqlite3.connect(self.db_path)
        conn.row_factory = sqlite3.Row
        return conn
    
    def create(self, emailqa_sop: EmailQA_SOP) -> EmailQA_SOP:
        """Create a new EmailQA_SOP association"""
        if self.association_exists(emailqa_sop.emailqa_id, emailqa_sop.sop_id):
            raise ValueError(f"Association between EmailQA {emailqa_sop.emailqa_id} and SOP {emailqa_sop.sop_id} already exists")
        
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO emailqa_sop (emailqa_id, sop_id, created_by)
                VALUES (?, ?, ?)
            """, (emailqa_sop.emailqa_id, emailqa_sop.sop_id, emailqa_sop.created_by))
            
            emailqa_sop.id = cursor.lastrowid
            
            # Get the created record with timestamp
            cursor.execute("""
                SELECT id, emailqa_id, sop_id, created_at, created_by
                FROM emailqa_sop
                WHERE id = ?
            """, (emailqa_sop.id,))
            
            row = cursor.fetchone()
            emailqa_sop.created_at = datetime.fromisoformat(row['created_at'])
            
            return emailqa_sop
    
    def get_by_id(self, association_id: int) -> Optional[EmailQA_SOP]:
        """Get EmailQA_SOP association by ID"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, emailqa_id, sop_id, created_at, created_by
                FROM emailqa_sop
                WHERE id = ?
            """, (association_id,))
            
            row = cursor.fetchone()
            if row:
                return EmailQA_SOP(
                    id=row['id'],
                    emailqa_id=row['emailqa_id'],
                    sop_id=row['sop_id'],
                    created_at=datetime.fromisoformat(row['created_at']),
                    created_by=row['created_by']
                )
            return None
    
    def get_all(self) -> List[EmailQA_SOP]:
        """Get all EmailQA_SOP associations"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, emailqa_id, sop_id, created_at, created_by
                FROM emailqa_sop
                ORDER BY created_at DESC
            """)
            
            associations = []
            for row in cursor.fetchall():
                associations.append(EmailQA_SOP(
                    id=row['id'],
                    emailqa_id=row['emailqa_id'],
                    sop_id=row['sop_id'],
                    created_at=datetime.fromisoformat(row['created_at']),
                    created_by=row['created_by']
                ))
            
            return associations
    
    def update(self, emailqa_sop: EmailQA_SOP) -> EmailQA_SOP:
        """Update an existing EmailQA_SOP association"""
        # Check if association exists
        existing = self.get_by_id(emailqa_sop.id)
        if not existing:
            raise ValueError(f"EmailQA_SOP association with ID {emailqa_sop.id} not found")
        
        # Check for duplicate association (excluding current record)
        if (emailqa_sop.emailqa_id, emailqa_sop.sop_id) != (existing.emailqa_id, existing.sop_id):
            if self.association_exists(emailqa_sop.emailqa_id, emailqa_sop.sop_id):
                raise ValueError(f"Association between EmailQA {emailqa_sop.emailqa_id} and SOP {emailqa_sop.sop_id} already exists")
        
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                UPDATE emailqa_sop
                SET emailqa_id = ?, sop_id = ?, created_by = ?
                WHERE id = ?
            """, (emailqa_sop.emailqa_id, emailqa_sop.sop_id, emailqa_sop.created_by, emailqa_sop.id))
            
            # Get updated record
            cursor.execute("""
                SELECT id, emailqa_id, sop_id, created_at, created_by
                FROM emailqa_sop
                WHERE id = ?
            """, (emailqa_sop.id,))
            
            row = cursor.fetchone()
            return EmailQA_SOP(
                id=row['id'],
                emailqa_id=row['emailqa_id'],
                sop_id=row['sop_id'],
                created_at=datetime.fromisoformat(row['created_at']),
                created_by=row['created_by']
            )
    
    def delete(self, association_id: int) -> bool:
        """Delete an EmailQA_SOP association by ID"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("DELETE FROM emailqa_sop WHERE id = ?", (association_id,))
            return cursor.rowcount > 0
    
    def get_by_emailqa_id(self, emailqa_id: int) -> List[EmailQA_SOP]:
        """Get all SOP associations for a specific EmailQA"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, emailqa_id, sop_id, created_at, created_by
                FROM emailqa_sop
                WHERE emailqa_id = ?
                ORDER BY created_at DESC
            """, (emailqa_id,))
            
            associations = []
            for row in cursor.fetchall():
                associations.append(EmailQA_SOP(
                    id=row['id'],
                    emailqa_id=row['emailqa_id'],
                    sop_id=row['sop_id'],
                    created_at=datetime.fromisoformat(row['created_at']),
                    created_by=row['created_by']
                ))
            
            return associations
    
    def get_by_sop_id(self, sop_id: int) -> List[EmailQA_SOP]:
        """Get all EmailQA associations for a specific SOP"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT id, emailqa_id, sop_id, created_at, created_by
                FROM emailqa_sop
                WHERE sop_id = ?
                ORDER BY created_at DESC
            """, (sop_id,))
            
            associations = []
            for row in cursor.fetchall():
                associations.append(EmailQA_SOP(
                    id=row['id'],
                    emailqa_id=row['emailqa_id'],
                    sop_id=row['sop_id'],
                    created_at=datetime.fromisoformat(row['created_at']),
                    created_by=row['created_by']
                ))
            
            return associations
    
    def get_sops_with_emailqa_by_subject(self, subject: str) -> List[dict]:
        """Get SOPs with associated EmailQA data by SOP subject"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT 
                    sop.id as sop_id,
                    sop.subject as sop_subject,
                    sop.sop as sop_content,
                    sop.created_at as sop_created_at,
                    sop.created_by as sop_created_by,
                    emailqa.id as emailqa_id,
                    emailqa.system_conversation_id,
                    emailqa.q_body,
                    emailqa.q_subject,
                    emailqa.q_real_question,
                    emailqa.q_question_subject,
                    emailqa.q_attachment_content,
                    emailqa.customer_service_answer,
                    emailqa.context_email_conversation_history_symmary,
                    emailqa.context_order_info,
                    emailqa.email_type,
                    emailqa_sop.id as association_id,
                    emailqa_sop.created_at as association_created_at,
                    emailqa_sop.created_by as association_created_by
                FROM emailqa_sop
                JOIN sop ON emailqa_sop.sop_id = sop.id
                JOIN emailqa ON emailqa_sop.emailqa_id = emailqa.id
                WHERE sop.subject LIKE ?
                ORDER BY sop.created_at DESC, emailqa_sop.created_at DESC
            """, (f"%{subject}%",))
            
            results = []
            for row in cursor.fetchall():
                results.append({
                    'sop': {
                        'id': row['sop_id'],
                        'subject': row['sop_subject'],
                        'sop': row['sop_content'],
                        'created_at': row['sop_created_at'],
                        'created_by': row['sop_created_by']
                    },
                    'emailqa': {
                        'id': row['emailqa_id'],
                        'system_conversation_id': row['system_conversation_id'],
                        'q_body': row['q_body'],
                        'q_subject': row['q_subject'],
                        'q_real_question': row['q_real_question'],
                        'q_question_subject': row['q_question_subject'],
                        'q_attachment_content': row['q_attachment_content'],
                        'customer_service_answer': row['customer_service_answer'],
                        'context_email_conversation_history_summary': row['context_email_conversation_history_symmary'],
                        'context_order_info': row['context_order_info'],
                        'email_type': row['email_type']
                    },
                    'association': {
                        'id': row['association_id'],
                        'created_at': row['association_created_at'],
                        'created_by': row['association_created_by']
                    }
                })
            
            return results
    
    def association_exists(self, emailqa_id: int, sop_id: int) -> bool:
        """Check if an association already exists"""
        with self._get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                SELECT COUNT(*) as count
                FROM emailqa_sop
                WHERE emailqa_id = ? AND sop_id = ?
            """, (emailqa_id, sop_id))
            
            row = cursor.fetchone()
            return row['count'] > 0